package com.example.demo5datajpa.domain;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * DeptRepository:
 *
 * @author : zhuyanpeng.zhuyanpeng
 * @date :  2019/6/13    0:08
 **/
public interface DeptRepository extends JpaRepository<Dept,Integer>, JpaSpecificationExecutor<Dept> {
        List<Dept> findByName(String name);

        List<Dept> findByIsTopTrue();

        List<Dept> findByIsTopFalse();

        @Query("from Dept d where d.name=:name")
        List<Dept> queryByName(@Param("name") String name);

        @Query("select d.name from Dept d where d.isTop=:isTop")
        List<String> queryNameAndIsTop(@Param("isTop") boolean isTop);

        @Transactional
        @Modifying
        @Query(value = "insert into dept (`name`,`order_no`,`is_top`) values(:name,:orderNo,0)",
                nativeQuery = true)
        int addDept(@Param("name") String name,@Param("orderNo") Integer orderNo);

        @Transactional
        @Modifying
        @Query(value = "delete from dept where name=:name AND order_no =:orderNo ",
                nativeQuery = true)
        int removeDept(@Param("name") String name,@Param("orderNo") Integer orderNo);

        @Transactional
        @Modifying
        @Query(value = "update Dept d set  d.name=:name where d.id=:id",nativeQuery = true)
        int changeNameById(@Param("name") String name,@Param("id") Integer id);


        @Query(value = "select new  com.example.demo5datajpa.domain.UserInfo(u.id,u.name,u.age,d.name AS deptName)"
        +"   from User AS u,Dept AS d,UserDept AS ud WHERE  u.id=:id AND u.id=ud.userId AND ud.deptId = d.id"
        )
        List<UserInfo> queryNameInfo(@Param("id") Integer id);

        /**
         * 查询部分字段
         * @param id
         * @return
         */
        @Query(value = "select new  com.example.demo5datajpa.domain.UserInfo(u.id,u.name,d.name AS deptName)"
                +"   from User AS u,Dept AS d,UserDept AS ud WHERE  u.id=:id AND u.id=ud.userId AND ud.deptId = d.id"
        )
        List<UserInfo> queryNameInfoSimple(@Param("id") Integer id);

        List<Dept> queryByIsTop(boolean isTop);

        @Transactional
        @Modifying
        @Query("update Dept d set  d.name=:#{#dept.name},d.orderNo=:#{#dept.orderNo} where d.id=:#{#dept.id}")
        int changeNameAndOrderNoById(@Param("dept") Dept dept);

        /*@Query(value = "update dept d set  d.name=:name,d.order_no=:orderNo where d.id=:id",nativeQuery = true)
        int changeNameAndOrderNoByIdSQL(Dept dept);*/



}
